package tt.dz.biz.dao.user;

import java.util.Date;
import java.util.List;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.stereotype.Repository;
import tt.dz.biz.dao.BaseDAO;
import tt.dz.common.LogUtil;
import tt.dz.vo.OperListVO;
import tt.dz.vo.OperRoleVO;
import tt.dz.vo.RoleVO;

@Repository("operRoleDAO")
public class OperRoleDAO
  extends BaseDAO
{
  private static final String LIST_ALL_OPERROLE = "select  a.manager_id,a.role_id,a.sys_code,a.remark,a.created_by,a.created_time,a.updated_by,a.updated_time,a.is_valid  from pub_oper_role as a ";
  private static final String INSERT_OPERROLE = "insert into pub_oper_role(manager_id,role_id,sys_code,remark,created_by,created_time,updated_by,updated_time,is_valid)  values(:managerId,:roleId,:sysCode,:remark,:createdBy,:createdTime,:updatedBy,:updatedTime,:isValid )";
  private static final String UPDATE_OPERROLE = "update  pub_oper_role set remark = :remark,updated_by = :updatedBy,updated_time = :updatedTime,is_valid = :isValid where manager_id = :managerId and role_id = :roleId and sys_code = :sysCode ";
  private static final String QUERY_OPERROLE_BY_ROLE = "select  a.manager_id,a.role_id,a.sys_code,a.remark,a.created_by,a.created_time,a.updated_by,a.updated_time,a.is_valid  from pub_oper_role as a  where a.role_id = :roleId";
  private static final String QUERY_HAS_OPERROLE = "select count(1) from pub_oper_role where manager_id = :managerId and role_id = :roleId and sys_code = :sysCode ";
  private static final String QUERY_OPERROLE_BY_OPER = "select pub_role.role_name,a.manager_id,a.role_id,a.sys_code,a.remark,a.created_by,a.created_time,a.updated_by,a.updated_time,a.is_valid  from pub_oper_role as a left join pub_role ON a.role_id = pub_role.role_id and a.sys_code = pub_role.sys_code where a.manager_id = :managerId and a.is_valid = 1 ";
  private static final String GET_OPERROLE_BY_OPER = "select pub_role.role_name,a.manager_id,a.role_id,a.sys_code,a.remark,a.created_by,a.created_time,a.updated_by,a.updated_time,a.is_valid  from pub_oper_role as a , pub_role where  a.role_id = pub_role.role_id  and  a.sys_code=pub_role.sys_code and   a.manager_id = :managerId and a.sys_code = :sysCode and a.is_valid = 1 and pub_role.is_valid=1";
  private static final String QUERY_UN_ALLOT_ROLE = "(select a.role_id,a.sys_code,a.role_name,a.remark from pub_role a where a.is_valid = 1 ) except (select a.role_id,a.sys_code,a.role_name,a.remark from pub_oper_role b  LEFT JOIN pub_role a on b.role_id = a.role_id and b.sys_code = a.sys_code where b.manager_id = :managerId and b.is_valid = 1 ) ";
  private static final String DELETE_OPERROLE = "update pub_oper_role set is_valid = 0 where manager_id = :managerId";
  private static final String QUERY_OPERROLE_OPERROLE = "select manager_id,role_id,sys_code,remark,created_by,created_time,updated_by,updated_time,is_valid from pub_oper_role where manager_id = :managerId and role_id = :roleId and sys_code = :sysCode ";
  
  public List<OperRoleVO> listAllOperRole()
  {
    return queryForList("select  a.manager_id,a.role_id,a.sys_code,a.remark,a.created_by,a.created_time,a.updated_by,a.updated_time,a.is_valid  from pub_oper_role as a ", null, OperRoleVO.class);
  }
  
  public List<OperRoleVO> queryOperRoleByRole(OperRoleVO operRoleVO)
  {
    return queryForList("select  a.manager_id,a.role_id,a.sys_code,a.remark,a.created_by,a.created_time,a.updated_by,a.updated_time,a.is_valid  from pub_oper_role as a  where a.role_id = :roleId", operRoleVO, OperRoleVO.class);
  }
  
  public List<RoleVO> queryUnAllotRole(OperListVO operListVO)
  {
    return queryForList("(select a.role_id,a.sys_code,a.role_name,a.remark from pub_role a where a.is_valid = 1 ) except (select a.role_id,a.sys_code,a.role_name,a.remark from pub_oper_role b  LEFT JOIN pub_role a on b.role_id = a.role_id and b.sys_code = a.sys_code where b.manager_id = :managerId and b.is_valid = 1 ) ", operListVO, RoleVO.class);
  }
  
  public List<OperRoleVO> queryOperRoleByOper(OperListVO operListVO)
  {
    return queryForList("select pub_role.role_name,a.manager_id,a.role_id,a.sys_code,a.remark,a.created_by,a.created_time,a.updated_by,a.updated_time,a.is_valid  from pub_oper_role as a left join pub_role ON a.role_id = pub_role.role_id and a.sys_code = pub_role.sys_code where a.manager_id = :managerId and a.is_valid = 1 ", operListVO, OperRoleVO.class);
  }
  
  public List<OperRoleVO> getOperRoleByOper(OperRoleVO aOperRole)
  {
    return queryForList("select pub_role.role_name,a.manager_id,a.role_id,a.sys_code,a.remark,a.created_by,a.created_time,a.updated_by,a.updated_time,a.is_valid  from pub_oper_role as a , pub_role where  a.role_id = pub_role.role_id  and  a.sys_code=pub_role.sys_code and   a.manager_id = :managerId and a.sys_code = :sysCode and a.is_valid = 1 and pub_role.is_valid=1", aOperRole, OperRoleVO.class);
  }
  
  public int[] batchInsertOperRole(List<OperRoleVO> operRoleList)
  {
    int[] iArray = new int[operRoleList.size()];
    OperRoleVO operRoleVO = null;
    for (int i = 0; i < operRoleList.size(); i++)
    {
      operRoleVO = (OperRoleVO)operRoleList.get(i);
      int count = queryForInt("select count(1) from pub_oper_role where manager_id = :managerId and role_id = :roleId and sys_code = :sysCode ", operRoleVO);
      if (count == 0)
      {
        if (operRoleVO.getCreatedBy() == null)
        {
          operRoleVO.setCreatedBy("owenor");
          operRoleVO.setCreatedTime(new Date());
        }
        iArray[i] = save("insert into pub_oper_role(manager_id,role_id,sys_code,remark,created_by,created_time,updated_by,updated_time,is_valid)  values(:managerId,:roleId,:sysCode,:remark,:createdBy,:createdTime,:updatedBy,:updatedTime,:isValid )", operRoleVO);
      }
      else
      {
        if (operRoleVO.getUpdatedBy() == null)
        {
          operRoleVO.setUpdatedBy("owenor1");
          operRoleVO.setUpdatedTime(new Date());
        }
        iArray[i] = save("update  pub_oper_role set remark = :remark,updated_by = :updatedBy,updated_time = :updatedTime,is_valid = :isValid where manager_id = :managerId and role_id = :roleId and sys_code = :sysCode ", operRoleVO);
      }
    }
    return iArray;
  }
  
  public int hasOperRole(OperRoleVO operRoleVO)
  {
    return queryForInt("select count(1) from pub_oper_role where manager_id = :managerId and role_id = :roleId and sys_code = :sysCode ", operRoleVO);
  }
  
  public int insertOperRole(OperRoleVO operRoleVO)
  {
    return save("insert into pub_oper_role(manager_id,role_id,sys_code,remark,created_by,created_time,updated_by,updated_time,is_valid)  values(:managerId,:roleId,:sysCode,:remark,:createdBy,:createdTime,:updatedBy,:updatedTime,:isValid )", operRoleVO);
  }
  
  public int updateOperRole(OperRoleVO operRoleVO)
  {
    return save("update  pub_oper_role set remark = :remark,updated_by = :updatedBy,updated_time = :updatedTime,is_valid = :isValid where manager_id = :managerId and role_id = :roleId and sys_code = :sysCode ", operRoleVO);
  }
  
  public int deleteOperRole(String managerId)
  {
    OperRoleVO operRoleVO = new OperRoleVO();
    operRoleVO.setManagerId(managerId);
    return save("update pub_oper_role set is_valid = 0 where manager_id = :managerId", operRoleVO);
  }
  
  public int deleteOperRole(String managerId, String sysCode)
  {
    StringBuilder sql = new StringBuilder("update pub_oper_role set is_valid = 0 where manager_id = :managerId");
    sql.append("  and sys_code=:sysCode");
    OperRoleVO operRoleVO = new OperRoleVO();
    operRoleVO.setManagerId(managerId);
    operRoleVO.setSysCode(sysCode);
    return save(sql.toString(), operRoleVO);
  }
  
  public OperRoleVO queryoperRoleByPrimaryKey(String managerId, String sysCode, String roleId)
  {
    try
    {
      OperRoleVO operRoleVO = new OperRoleVO();
      operRoleVO.setManagerId(managerId);
      operRoleVO.setSysCode(sysCode);
      operRoleVO.setRoleId(roleId);
      return (OperRoleVO)queryForObject("select manager_id,role_id,sys_code,remark,created_by,created_time,updated_by,updated_time,is_valid from pub_oper_role where manager_id = :managerId and role_id = :roleId and sys_code = :sysCode ", operRoleVO, OperRoleVO.class);
    }
    catch (EmptyResultDataAccessException e)
    {
      LogUtil.error("未查到操作员的角色信息，参数为：managerId=" + managerId + ",sysCode=" + sysCode + ",roleId=" + roleId, e);
    }
    return null;
  }
}
